# -coding:utf-8-*-
"""
数据库
创建engine
销毁连接
"""
import pandas as pd
import traceback
from sqlalchemy import create_engine
import numpy as np
# 将数据写入数据库
con = create_engine(
    'mysql+pymysql://root:root@localhost:3306/stack?charset=utf8')


def dataframe_tosql(pd_data2, table_name):
    """
    将pandas  dataFrame 数据直接写入数据库
    """
    try:

        pd.io.sql.to_sql(pd_data2, table_name, con,
                         if_exists="append", index=False)
    except Exception as ee:
        print("导入数据库失败")
        print("error >>>", ee)
        traceback.print_exc()
    else:
        print("导入数据库-end---"+table_name)
    finally:
        pass


def dataframe_tosql_(pd_data2, table_name,dictdtype):
    """
    将pandas  dataFrame 数据直接写入数据库
    将索引表名设置为index_
    """
    try:

        pd.io.sql.to_sql(pd_data2, table_name, con,
                         if_exists="append", index=False,index_label='index_',dtype=dictdtype)
    except Exception as ee:
        print("导入数据库失败")
        print("error >>>", ee)
        traceback.print_exc()
    else:
        print("导入数据库-end---"+table_name)
    finally:
        pass

def getdata_from_sql(sql_):
    """
    从数据库返回 DataFrame 数据
    """
    data = pd.read_sql(sql_, con)
    return data

def getdata_from_sql_query(sql_,columns):
    """
    从数据库返回 DataFrame 数据
    """
    data = pd.read_sql(sql_, con,columns=columns)
    return data
def getdata_from_sql_tolist(sql_):
    """
    从数据库返回 list数据
    """
    data=getdata_from_sql(sql_)
    data_=np.array(data)
    data_list=data_.tolist()
    return data_list

def dbcon_dispose():
    """
    关闭数据库连接
    """
    con.dispose()
